<?php

namespace App\Helpers;

use Exception;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Protection;

/**
 * Class OfficeHelper
 */
class OfficeHelper extends Helper
{
	/**
	 * 根据列数生成Excel的坐标(A1, B1... AA1, AB1)
	 * Excel限制
	 * 在 Excel 2010 和 Excel 2007 中，工作表的大小为 16,384 列 X 1,048,576 行，
	 * 而在 Excel 97-2003 中，工作表的大小仅为 256 列 X 65,536 行。
	 *
	 * @param int    $columns
	 * @param string $row
	 * @param bool   $newVersion 版本大于等于2007
	 *
	 * @return array
	 */
	private static function makeExcelCoordinates(int $columns = 1, string $row = '', bool $newVersion = true): array
	{
		if ($newVersion) {
			if ($columns > 16384 || $row > 1048576) {
				return [];
			}
		} else {
			if ($columns > 256 || $row > 65536) {
				return [];
			}
		}

		$coordinates = [];
		for ($i = 0; $i < $columns; ++$i) {
			$coordinates[] = self::makeExcelCoordinate($i, $row);
		}

		return $coordinates;
	}

	/**
	 * 生成第$rows列的坐标
	 *
	 * @param int    $column
	 * @param string $row 第$row行
	 *
	 * @return string
	 */
	private static function makeExcelCoordinate(int $column = 1, string $row = ''): string
	{
		$first = 65;

		$step = intval($column / 26);
		$mod  = $column % 26;

		$cell   = chr($first + $mod);
		$prefix = $step == 0 ? '' : chr($first + $step - 1);

		return $prefix . $cell . $row;
	}

	/**
	 * 加工文件path
	 *
	 * @param string $fileName
	 * @return string
	 */
	private static function makeFilePath(string $fileName): string
	{
		// 处理不同环境的日志目录
		if (env('APP_ENV') == 'local') {
			$pathDir = storage_path('file/') . 'office/';
		} else {
			$pathDir = env('PUBLIC_FILE_PATH', '/opt/www_pets/tmp/pets_api/') . 'office/';
		}

		// 目录处理
		if (!is_dir($pathDir)) {
			mkdir($pathDir, 0777, true);
		}

		$fileName .= '_' . formatDate('YmdHis') . rand(100000, 999999) . '.xlsx';

		return $pathDir . $fileName;
	}

	/**
	 * 保存内容为excel
	 *
	 * $mergeCell为合并单元格，
	 * 数据结构：
	 *  [
	 *      [
	 *          [1, 0], [2, 0]
	 *      ],
	 *      [
	 *          [1, 1], [2, 1]
	 *      ]
	 *  ]
	 * 其中数字为导出数据在数组中的下标
	 * 如上表示 从元素 1,0 合并到元素 2,0，对应单元格为：A1:B1
	 * 如果有多个tab（即 $multiple 为true），则该字段外面再加一层数组
	 *$lockArr 格式同$mergeCell
	 *
	 * @param array  $dataList
	 * @param string $filename
	 * @param bool   $local
	 * @param bool   $csv
	 * @param bool   $multiple
	 * @param array  $mergeCell
	 * @param array  $lockCell
	 * @return string
	 */
	public static function writeToExcel(array $dataList, string $filename, bool $local = false, bool $csv = true, bool $multiple = false, array $mergeCell = [], array $lockCell = []): string
	{
		// 加工文件path
		$filename = static::makeFilePath($filename);
		if (!$multiple) {
			$dataList  = [$dataList];
			$mergeCell = [$mergeCell];
			$lockCell  = [$lockCell];
		}

		if ($csv) {
			$contentType = 'text/csv; charset=UTF-8';
		} else {
			// Excel 2007
			$contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8';
			// Excel5
			//$contentType = 'application/vnd.ms-excel; charset=UTF-8';
		}

		try {
			$excel = new Spreadsheet();

			$i = 1;
			while ($i++ < count($dataList)) {
				$excel->createSheet();
			}

			$index = 0;
			foreach ($dataList as $title => $data) {
				$excel->setActiveSheetIndex($index);
				$activeSheet = $excel->getActiveSheet();

				if (!is_numeric($title)) {
					$activeSheet->setTitle($title);
				}

				$rows           = 0;
				$coordinatesArr = [];

				foreach ($data as $line) {
					$coordinatesArr[] = $coordinates = self::makeExcelCoordinates(count($line), ++$rows);

					for ($i = 0; $i < count($line); ++$i) {
						// 过长的数字以字符串显示，以免科学计数法
						if (is_numeric($line[$i])) {
							$activeSheet->setCellValueExplicit(
								$coordinates[$i],
								$line[$i],
								strlen($line[$i]) >= 10 ? DataType::TYPE_STRING : DataType::TYPE_NUMERIC
							);
						} elseif (is_string($line[$i])) {
							$activeSheet->setCellValueExplicit(
								$coordinates[$i],
								$line[$i],
								DataType::TYPE_STRING
							);
						} else {
							$activeSheet->setCellValue($coordinates[$i], $line[$i]);
						}
					}
				}

				// 合并单元格
				if (isset($mergeCell[$index])) {
					foreach ($mergeCell[$index] as $mergeCellItem) {
						// $pRange = A1:A3 表示从A1合并到A3
						$pRange = $coordinatesArr[$mergeCellItem[0][0]][$mergeCellItem[0][1]] . ':' . $coordinatesArr[$mergeCellItem[1][0]][$mergeCellItem[1][1]];

						$activeSheet->mergeCells($pRange);
					}
				}
				//设置可编辑范围
				if ($lockCell[$index]) {
					$activeSheet->getProtection()
						->setSheet(true);
					foreach ($lockCell[$index] as $lockCellRange) {
						$activeSheet->getStyle($coordinatesArr[$lockCellRange[0][0]][$lockCellRange[0][1]] . ':' . $coordinatesArr[$lockCellRange[1][0]][$lockCellRange[1][1]])
							->getProtection()
							->setLocked(Protection::PROTECTION_UNPROTECTED);
					}
				}
				++$index;
			}

			if ($csv) {
				$writer = IOFactory::createWriter($excel, 'Csv');
				$writer->setUseBOM(true);
			} else {
				$writer = IOFactory::createWriter($excel, 'Xlsx');
			}

			if ($local) {
				$writer->save($filename);
			} else {
				header('Content-Type: ' . $contentType);
				header('Content-Disposition: attachment; filename=' . $filename);
				header('Content-Transfer-Encoding: binary');
				header('Cache-Control: must-revalidate, post-check=0, pre-check=0');

				$writer->save('php://output');
			}
			return $filename;
		} catch (Exception $ex) {
			LogHelper::exLog($ex, 'phpOffice');
			return '';
		}
	}

	/**
	 * 根据索引生成对应的列
	 *
	 * @param int $pColumnIndex
	 *
	 * @return string
	 */
	private static function stringFromColumnIndex(int $pColumnIndex = 0): string
	{
		return Coordinate::stringFromColumnIndex($pColumnIndex);
	}

	/**
	 * 读取excel内容返回，组装好的数据和总条数
	 *
	 * @param array  $title        标题
	 * @param string $import_excel 表单字段name
	 *
	 * @return array
	 */
	public static function readExcel(array $title, string $import_excel = 'import_excel'): array
	{
		try {
			$res_excel = $_FILES[$import_excel];
			//验证文件是否存在
			if (!file_exists($res_excel['tmp_name']) || !is_file($res_excel['tmp_name']) || !is_readable($res_excel['tmp_name'])) {
				throw new Exception('文件不存在');
			}

			//excel取出的数据
			$excel_data = [];
			// 导入第一列为序号，所以要占位
			array_unshift($title, '');
			$count = count($title);

			$excel = IOFactory::load($res_excel['tmp_name']);
			//选择标签页
			$sheet = $excel->getSheet(0);
			//获取总行数
			$total_row = $sheet->getHighestRow();

			for ($i = 2; $i <= $total_row; ++$i) {
				$row = [];
				// 行状态， 默认为false无效,  如果title对应的列有值，则认为此行有效
				$row_status = false;
				for ($j = 1; $j < $count; ++$j) {
					$cellName = OfficeHelper::stringFromColumnIndex($j) . $i;
					$cellVal  = trim($sheet->getCell($cellName)
						->getValue());
					if ($cellVal) {
						$row_status = true;
					}

					$this_title       = $title[$j];
					$row[$this_title] = $cellVal;
				}

				// 如果此行title对应的所有列数据为空，则中断循换：excel 粘贴容易出现空白行
				if ($row_status === false) {
					$total_row = --$i;
					break;
				}

				$excel_data[$i] = $row;
			}
		} catch (Exception $ex) {
			LogHelper::exLog($ex, 'phpOffice');

			return [];
		}

		return [$excel_data, $total_row];
	}
}
